﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Model;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.IO;
using System.Reflection;

namespace DAL
{
    public class DBhelp
    {
        private DBhelp() { }
        private static DBhelp dbhelp = null;
        public static DBhelp Create()
        {
            if (dbhelp == null)
                dbhelp = new DBhelp();
            return dbhelp;
        }

        string conString = ConfigurationManager.ConnectionStrings["a"].ConnectionString;

        public static bool  BakupLibrary(string BakupLibraryName)
        {       string conmaster = ConfigurationManager.ConnectionStrings["master"].ConnectionString;
                //string sql = "SELECT * FROM sys.databases WHERE name = 'Library'";
            string sql = $"BACKUP DATABASE Library TO DISK = '{BakupLibraryName}'";
            SqlConnection con = new SqlConnection(conmaster);
            try
            {
              
                con.Open();
                SqlCommand com = new SqlCommand(sql, con);
                com.ExecuteNonQuery();
                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                con.Close();
            }
        }


        public static bool initLibrary()
        {
            string conmaster = ConfigurationManager.ConnectionStrings["master"].ConnectionString;
            string sql = "SELECT * FROM sys.databases WHERE name = 'Library'";
            SqlConnection con = new SqlConnection(conmaster);
            try
            {
                con.Open();
                SqlCommand com = new SqlCommand(sql, con);
                SqlDataAdapter adapter = new SqlDataAdapter(com);
                DataSet ds = new DataSet();
                adapter.Fill(ds, "a");
                if (ds.Tables[0].Rows.Count > 0)
                {                 //com.Parameters.AddRange(sp);
                    return true;
                }
                else
                {
                    string strCreat = " CREATE DATABASE Library ON PRIMARY ( NAME = N'[Library]', FILENAME = N'D:\\Library\\Library.mdf', SIZE = 5120KB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )" +
                        "LOG ON( NAME = N'Library_log', FILENAME = N'D:\\Library\\Library_log.ldf',SIZE = 1024KB, MAXSIZE = 2048GB, FILEGROWTH = 10%)";
                    com = new SqlCommand(strCreat, con);
                    com.ExecuteNonQuery();
                   
                    //先建数据库然后初始化表
                    string resourceName = "DAL.Library.sql";

                    // 获取当前程序集
                    Assembly assembly = Assembly.GetExecutingAssembly();

                    // 读取嵌入的资源，假设资源名为 "YourNamespace.YourTextFile.txt"
                    // 其中 "YourNamespace" 是你的项目命名空间，"YourTextFile.txt" 是文件名
                    using (Stream stream = assembly.GetManifestResourceStream(resourceName))
                    using (StreamReader reader = new StreamReader(stream))
                    {
                        string result = reader.ReadToEnd();
                        ExecuteSqlScript(ConfigurationManager.ConnectionStrings["a"].ConnectionString, result);
                    }                    
                    //执行初始化操作
                    return true;
                }
            }
            catch (Exception)
            {
                con.Close();
               return false;
            }
            finally
            {
                con.Close();
            }
        }
        static void ExecuteSqlScript(string connectionString, string script)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // string [] sqls = File.ReadAllText(scriptPath).Split("GO");
                string[] sqls = script.Replace("GO","#").Split('#');

                foreach (var sql in sqls)
                {
                    using (SqlCommand command = new SqlCommand(sql, connection))
                    {
                        command.ExecuteNonQuery();
                    }
                }
                connection.Close();
            }
        }
        //返回一行一列    
        public int ExecuteScalar(string sql, params SqlParameter[] sp)
        {
            SqlConnection con = new SqlConnection(conString);

            try
            {
                con.Open();
                SqlCommand com = new SqlCommand(sql, con);
                com.Parameters.AddRange(sp);
                return (int)com.ExecuteScalar();
            }
            catch (Exception)
            {
                con.Close();
                throw;
            }
            finally
            {
                con.Close();
            }
            
        }

        //返回读取器对象
        public SqlDataReader ExecuteReader(string sql, params SqlParameter[] sp)
        {
            SqlConnection con = new SqlConnection(conString);
            try
            {
                con.Open();
                SqlCommand com = new SqlCommand(sql, con);
                com.Parameters.AddRange(sp);
                return com.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                con.Close();
                throw ex;
            }
        }

        //返回数据集，
        public DataSet ExecuteAdater(string sql, params SqlParameter[] sp)
        {
            SqlConnection con = new SqlConnection(conString);

            try
            {
                SqlCommand com = new SqlCommand(sql, con);
                com.Parameters.AddRange(sp);
                SqlDataAdapter adapter = new SqlDataAdapter(com);
                DataSet ds = new DataSet();
                adapter.Fill(ds, "a");
                return ds;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.Close();
            }


        }

        //返回受影响行数
        public int ExecuteNonQuery(string sql, CommandType type = CommandType.Text, params SqlParameter[] sp)
        {
            SqlConnection con = new SqlConnection(conString);
            try
            {
                con.Open();
                SqlCommand com = new SqlCommand(sql, con);
                com.Parameters.AddRange(sp);
                com.CommandType = type;
                return com.ExecuteNonQuery();
            }
            catch (Exception)
            {
                con.Close();
                throw;
            }
            finally
            {
                con.Close();
            }




        }


    }
}
